Datenbanken
Grundlagen Elemente im ER-Diagramm Entity (Objekt, Gegenstand, Entität) Bild:Entity.png ein real oder begrifflich existierender Gegenstand mit fester, bekannter Menge von Eigenschaften (Attributen), der eigenständig mit Hilfe einer Teilmenge seiner Eigenschaften eindeutig identifiziert werden kann (Schlüssel, key); z.B.: * der Angestellte "Müller", der über eine eindeutige Personalnummer, einen Familien- sowie Vornamen, ein Einstellungsdatum und ein bestimmtes Gehalt verfügt Bild:Angestellter.png * die Abteilung "Einkauf", die über eine eindeutige Abteilungsnummer, eine Abteilungsbezeichnung sowie eine Standortangabe verfügt Bild:Abteilung.png ---- weak Entity (schwache Entität) Bild:Weak_entity.png ID-Dependency (Identifikationsabhängigkeit) ein weak Entity kann nur in Beziehung zu einem anderen Entity (parent entity) identifiziert werden z.B.: * Kinder, für die ein Angestellter Familienbeihilfe bezieht, können nur über die Personalnummer des betreffenden Angestellten identifiziert werden E-Dependency (Existenzabhängigkeit) ein weak Entity existiert in der Datenbank nur, wenn ein anderes Entity auch existiert z.B.: * verlässt ein Angestellter das Unternehmen, so sind auch die Daten seiner Kinder für dieses Unternehmen uninteressant geworden Bild:Angestellter_kind.png ---- Entity Type (Entityklasse, Objektklasse, Objekttyp eine Klasse (Menge) von gleichartig zusammengesetzten, eindeutig identifizierbaren Entities, z.B.: * "Angestellter Müller" und "Angestellter Bürger" sind in der Klasse "Angestellter" zusammengefasst Elemente einer Entityklasse werden als Exemplare bzw. Instanzen bezeichnet, z.B.: * "Müller" und "Bürger" sind Instanzen der Klasse "Angestellter" ---- Relationship (Assoziation, Beziehung) Bild:Relationship.png Beziehung zwischen Objekten, z.B.: * der "Angestellte Müller" ist in der "Abteilung Verkauf" beschäftigt ---- Relationship Type (Assoziationstyp, Beziehungstyp) abstrakte Beziehung zwischen Objekttypen, z.B.: * ein Angestellter ist in einer Abteilung beschäftigt Bild:Angestellter abteilung.png ---- Rolle (role) Funktion, die ein Objekt in einer Beziehung erfüllt, z.B.: * ein Angestellter ist Leiter einer Abteilung ---- Attribute (Attribut, Merkmal, Charakteristik Bild:Attribut.png beschreibende Eigenschaften von Entities (bzw. Entitytypen) und Relationships * Personalnummer, Familienname, Vorname, Einstellungsdatum eines Angestellten * Erstinskriptionsdatum des Studenten für jedes Studium, das er studiert (ein Student kann mehrere Studienrichtungen studieren) Bild:Student studiert.png ---- Value (Attributsausprägung, Merkmalausprägung, Wert) Wert eines Attributs für ein bestimmtes Entity bzw. eine bestimmte Assoziation, z.B.: * Angestellter Müller hat die Personalnummer 0815 ---- Domain (Wertebereich, value set) Menge der für ein Attribut möglichen Werte, z.B.: * Angestelltenname {Müller, Mair, Bürger, ...} ---- Key (Schlüssel, eindeutige Identifikation) Bild:Key.png Merkmal, dessen Ausprägung ein bestimmtes Entity eindeutig von allen anderen Entities der selben Klasse unterscheidet, z.B.: * Personalnummer für Entity "Angestellte" (es gibt nur einen Angestellten mit Nr. 0815) * Abteilungsnummer für Entity "Abteilung" (es gibt nur eine Abteilung mit Nr. 4711) ein Schlüssel kann auch aus einer Kombination mehrerer Attribute bestehen z.B.: * ISBN-Nr u. Exemplarnummer gemeinsam bestimmen ein Buch eindeutig ---- Assoziationstypen Grad der Beziehung * Anzahl der durch den Assoziationstyp verbundenen Entitytypen * binäre Relationships (Beziehung betrifft 2 Entities) * komplexe (n-äre) Relationships (Beziehung betrifft mehr als 2 Entities) Bild:Chef erteilt aufgabe.png Bild:Chef mitarbeiter veranstaltung.png Konnektivität * spezifiziert die Zuordnungswertigkeit (Obergrenze) einer Beziehung (n:m, 1:n, 1:1) * Untergrenze = 0 Binäre Assoziationstypen * m:n (many-to-many) * 1:n (one-to-many) bzw. n:1 (many-to-one) * 1:1 (one-to-one) m:n Bild:N_zu_m.png ---- 1:n bzw. n:1 Bild:1zun.png ---- 1:1 Bild:1zu1.png ---- Rekursive Beziehungen Bild:Rekursive beziehungen.png Ein Projekt besteht aus mehreren Teilprojekten. Ein Teilprojekt gehört zu einem Projekt. Ein Kurs setzt n Vorbedingungen voraus. Ein Kurs erteilt wiederrum m Berechtigungen. ---- Konsistenzbedingungen (constrains) ... sind Einschränkungen der zulässigen Datenbankzustände, die nicht mit den Konzepten des Datenmodells ausdrückbar sind, z.B.: * Abflugzeit < Ankunftszeit Kardinalitäten bei Attributen Bild:Kardinalitaeten.png * Minimale Kardinalität: wie oft kommt das Attribut bei jeder Instanz mindestens vor * Maximale Kardinalität: wie oft kommt das Attribut bei jeder Instanz höchstens vor Kardinalitäten vs. Konnektivitäten (bei Entitys) Bild:Kardinalitaeten_konnektivitaeten.png oben: Konnektivitäten, unten Kardinalitäten * Minimale Kardinalität: wie oft nimmt jedes Entity mindestens an der Beziehung teil * Maximale Kardinalität: wie oft nimmt jedes Entity höchstens an der Beziehung teil * ACHTUNG: umgekehrte Leserichtung als bei Konnektivitäten!!! * Bsp.: Studenten können maximal drei Studienrichtungen studieren Generalisierung/Spezialisierung Bild:Generalisierung spezialisierung.png logische Verbindung: is-a, ISA, ist-ein zwischen * einem "Eltern"-Entitytyp E * und mehreren "Kind"-Entitytypen K1, K2,..., Kn * E ist allgemeiner als die Ki * E ist eine Generalisierung (Verallgemeinerung) der Ki * die Ki sind eine Spezialisierung von E ---- Generalisierungsarten Bild:Generalisierungsarten.png Aggregation (part-of, POF, Teil-von) * Zusammenfassung unterschiedlicher Entitytypen, die zusammen einen strukturierten Objekttyp bilden * Oft zur Betonung wirklicher physischer Strukturierung verwendet: Ist Teil von, ist Komponente von, ist eingebaut in, ... * In der Regel: 1:n Beziehungen (ein Teil ist nur in einem Ganzen enthalten, ein Ganzes kann aber mehrere Teile enthalten) * Oft damit verbunden: Existenzabhängigkeit oder ID-Abhängigkeit Aggregatfunktionen Aggregation (auch Konsolidierung oder Verdichtung) bezeichnet, im Zusammenhang mit der Verwaltung großer Datenmengen in einem Data-Warehouse, das Zusammenfassen einer Reihe von Fakten zu einem einzelnen Fakt. Beispielsweise lassen sich aus einer Menge von Zahlen der Mittelwert, das Minimum bzw. Maximum oder die Summe bestimmen. Solche Funktionen, die einer Menge von Zahlen einen einzelnen Wert zuordnen, nennt man Aggregationsfunktionen (oder Zusammenfassungsfunktionen). Die bei einer Aggregation verwendeten Funktionen lassen sich unterteilen in * Distributive Funktionen: Summe, Anzahl, Maximum, Minimum... * Algebraische Funktionen: Mittelwert, Standardabweichung, Top-N... Relationenmodell Bedeutung → Repräsentation Entitätstyp → Tabellenname Attribut → Spaltenbezeichnung Merkmalausprägung → Wert konkrete Entität → Zeile Bild:Student tabelle.png Bild:Student.png ---- Relationenmodell - Begriffserklärungen Relation Eine Relation ist eine mit konkreten Merkmalsausprägungen gefüllte Tabelle, deren Zeilen sich voneinander (mindestens an einer Position) unterscheiden (Mengeneigenschaft) und deren Spalten jeweils den Namen eines Attributs tragen. Eine Relation besteht aus einer Menge von Tupeln. * Stelligkeit, Grad (arity) einer Relation = Anzahl ihrer Spalten * Kardinalität (cardinality) einer Relation = Anzahl ihrer Tupel (Zeilen) Tupel * Ein Tupel stellt eine konkrete Ausprägung eines Entitätstyps dar und entspricht einer Zeile in einer Tabelle, die eine Relation repräsentiert. * Tupel sind Elemente einer Relation. * Ein Tupel ist eine spezielle Ausprägung der Kombination der Attributwerte. Attribut (Feld) * Spalte der Relation (des Tupels); kann Werte aus einer Domäne annehmen. Wertebereich (Domäne, Domain) * Menge aller denkbaren Ausprägungen der Werte für diesen Datentyp Daten (Werte, values) *Daten sind konkrete Attributwerte (Merkmalsausprägungen), die (semantisch) nicht weiter zerlegbar sind. Nullwerte Jeder Wertebereich wird um einen Wert NULL erweitert, der verschieden von allen anderen Werten ist. * mögliche Bedeutung von Nullwerten: ** unbekannt z.B. Geburtsdatum einer Person ** nicht anwendbar z.B. TelNr bei einer Person, die kein Telefon hat Darstellung: * Null * ω Abstraktion, Implementation Bild:Abstraktion implementation.png ER-Schema in relationales Schema transformieren * Aus jedem Entity Type (Objektklasse) wird eine Relation, die alle Attribute der Objektklasse enthält. * Aus jeder n:m-Beziehung wird eine Relation, welche die Schlüsselattribute der beiden durch die Beziehung verbundenen Entitytypen sowie eventuelle Attribute der Beziehung selbst enthält; den Schlüssel dieser Relation bildet im allgemeinen die Vereinigungsmenge der Schlüsselattribute der beteiligten Entity Types (Ausnahmen!). * 1:n-Beziehungen werden aufgelöst; sie werden nicht in Relationen übergeführt. Der Schlüssel des "1-Entity-Type" wird als Fremdschlüssel in die Relation des "n-Entity-Type" hineingezogen. Der letzteren Relation werden ebenfalls eventuell vorhandene Attribute der Beziehung zugeführt. * 1:1-Beziehungen werden aufgelöst. Als Entwurfsentscheidung bleibt die Überlegung, welcher Schlüssel in die jeweils andere Relation hineinzuziehen ist. Dabei ist zu berücksichtigen, wo die Wahrscheinlichkeit des Vorhandenseins von konkreten Datenwerten höher ist. Bild:Beispiel_betrieb.png Bild:Beispiel_betrieb_tabellenform.png Bild:Universitaet.png Beispiele Uni-Datenbank Im weiteren Verlauf des Abschnitts werden viele SQL-Befehle erklärt; Grundlage ist dabei das folgende Beispiel: Bild:Uni_beispiel_wikipedia.png Beispiele: SELECT * FROM Student : Listet die Werte aller Spalten aus der Tabelle Student auf. SELECT MatrNr FROM Student : Projektion: Listet die Spalte MatrNr der Tabelle Student auf. SELECT DISTINCT PersNr FROM Vorlesung : Projektion: Listet die vorhandenen, verschiedenen Ausprägungen der Spalte PersNr aus der Tabelle Vorlesung auf. SELECT MatrNr AS Matrikelnummer, Name FROM Student : Die Spalte MatrNr heißt in der Ergebnisrelation jetzt Matrikelnummer. SELECT VorlNr, Titel FROM Vorlesung WHERE PersNr = 12 : Selektion: Listet alle Vorlesungen des Professors mit der Personalnummer '12' auf. SELECT a.VorlNr, a.Titel, b.PersNr, b.Name FROM Professor b INNER JOIN Vorlesung a ON b.PersNr = a.PersNr : Innerer natürlicher Verbund: Alle Datensätze aus den Tabellen Professor und Vorlesung, die den gleichen Wert im Feld PersNr haben. Professoren ohne Vorlesung und Vorlesungen ohne Professor sind nicht enthalten. SELECT a.VorlNr, a.Titel, b.PersNr, b.Name FROM Professor b LEFT OUTER JOIN Vorlesung a ON b.PersNr = a.PersNr : Äußerer linker natürlicher Verbund: Alle Datensätze der Tabelle Professor und alle Datensätze aus beiden Tabellen, die den gleichen Wert im Feld PersNr haben. Professoren ohne Vorlesung sind enthalten, Vorlesungen ohne Professor sind nicht enthalten. SELECT b.PersNr, b.Name FROM Professor b LEFT OUTER JOIN Vorlesung a ON b.PersNr = a.PersNr WHERE a.PersNr IS NULL : Äußerer linker natürlicher Verbund, Selektion und Projektion: Alle Professoren, die keine Vorlesungen halten. SELECT a.PersNr, a.Name FROM Professor a WHERE NOT EXISTS (SELECT * FROM Vorlesung WHERE PersNr = a.PersNr) : Unterabfrage mit Existenz-Quantor: Das gleiche mit einer Unterabfrage. SELECT COUNT(b.PersNr) AS Anzahl, a.PersNr, a.Name FROM Professor a LEFT OUTER JOIN Vorlesung b ON a.PersNr = b.PersNr GROUP BY a.Name, a.PersNr : Äußerer linker natürlicher Verbund und Gruppierung, Aggregation: Zählt die Anzahl der Vorlesungen pro Professor. : Merke: COUNT(a.PersNr) oder COUNT(*) wären falsch (Nullwerte sollen nicht mitgezählt werden). Übung 5 3d select count (a.name) from professoren p, assistenten a where a.boss = p.persnr and p.name = 'Curie' 3e select count (a.name) from professoren p, assistenten a where a.boss = p.persnr and p.name in ('Sokrates', 'Russel', 'Augustinus') 3j select avg (p.note) from pruefen p, vorlesungen v where p.vorlnr = v.vorlnr and v.titel = 'Die 3 Kritiken' 3k select count (s.name) from professoren r, pruefen p, studenten s where r.name = 'Sokrates' and s.matrnr = p.matrnr and p.persnr = r.persnr g select v2.* from vorlesungenv2, professoren p2 where p2.persnr=v2.gelesenvon and p2.rang='C3' and v2.sws <=( select min(sws) from vorlesungen v, professoren p where v.gelesen=p.pers and p.rang ='C4' and raum in (232,321) ) h select s.matrnr,s.name,s.semester,count(*) from studenten s, pruefen pr where s.matrnr=pr.matrnr group by s.matrnr, s.name,s.semester having count(*)= ( select max(p.anzahl) from( select count(*) as anzahl from pruefen group by matrnr )p ) i korrellierte subquery select s.* from studenten s, pruefen p where p.matrnr=s.matrnr and not exists ( select note from pruefen p2 where note > 1 and p2.matrnr=s.matrnr ) k select count(distinct matrnr) from pruefen p, professoren pr where p.vorlnr=pr.persnr and pr.name='Sokrates' Übung 4 select name from studenten select persnr, rang from professoren select raum from professoren where rang = 'C3' select * from vorlesungen where sws < 4 select * from studenten where semester = 2 OR semester = 4 OR semester = 6 OR semester = 10 alternative: where semester in (2, 4, 6, 10) select a.* from assistenten a, professoren p where a.boss = p.persnr and p.rang = 'C4' select v.* from professoren p, vorlesungen v where v.gelesenvon = p.persnr and p.name = 'Kant' select s.name, s.matrnr from studenten s, vorlesungen v, hoeren h where s.matrnr=h.matrnr and v.vorlnr=h.vorlnr and v.vorlnr=4630 select s.* from studenten s, vorlesungen v, professoren p, hoeren h where s.matrnr=h.matrnr and v.vorlnr=h.vorlnr and v.gelesenvon=p.persnr and p.name = 'Kant' select s.* from studenten s, vorlesungen v, professoren p, hoeren h where s.matrnr=h.matrnr and v.vorlnr=h.vorlnr and v.gelesenvon=p.persnr and p.name = 'Kant' select distinct v.titel from vorlesungen v, hoeren h, studenten s where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr and s.name in ('Fichte', 'Schopenhauer') select v.* from vorlesungen v, assistenten a, professoren p where a.name = 'Wittgenstein' and a.boss = p.persnr and p.persnr = v.gelesenvon select * from vorlesungen except select v.* from vorlesungen v, hoeren h where v.vorlnr=h.vorlnr Übungsklausur 1.) Wie viele Gemälde von ’Kokoschka’ umfasst die Sammlung ’Leopold’? SELECT COUNT (g.gemaeldenummer) FROM sammlung s, gemaelde g WHERE g.kuenstler = 'Kokoschka' AND s.sammlung = 'Leopold’ AND g.sammlungsname = s.sammlungsname 2.) Welche Künstler haben im 20. Jahrhundert mehr als 10 Gemälde geschaffen? SELECT kuenstler FROM gemaelde WHERE entstehungsdatum >= 01.01.1900 AND entstehungsdatum < 01.01.2000 and 10 < (SELECT SUM FROM gemaelde GROUP BY kuenstler) 2.) Test von Denise: Welche Künstler haben mehr als 10 Gemälde geschaffen? SELECT kuenstler, count( * ) AS anzahl FROM gemaelde GROUP BY kuenstler HAVING count( * ) >10 3.) Welche Gemälde (Nummer, Titel, Künstler) hingen im Jahr 2001 in Räumen, die nicht klimatisiert waren oder eine Beleuchtungsstufe größer 3 hatten? SELECT g.gemaeldenummer, g.titel, g.kuenstler FROM gemaelde g, raum r, ausgestellt a WHERE a.gemaeldenummer = g.gemaeldenummer AND r.raumnummer = a.raumnummer AND (r.klimatisiert = 'N' OR r.belauchtungsstufe > 3) AND a.von >= 01.01.2001 AND a.bis <> 31.12.2001 4.) Welche der Ausstellungen (Bezeichnung, Sponsor) haben nur Gemälde von ‘Giotto’ oder ‘Tiepolo’ gezeigt? SELECT b.bezeichnung, b.sponsor FROM austellung b, gemaelde g, ausgestellt a WHERE g.gemaeldenummer = a.gemaeldenummer AND a.bezeichnung = b.bezeichnung AND (g.kuenstler = 'Giotto' OR g.kuenstler = 'Tiepolo') and except select ?????? die wegselektieren, die eine ausstellung hatten aber anderer künstler